# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
from poetry.items import *
from pymysql.err import DataError


class MysqlPipeline:
    def __init__(self):

        self.db = pymysql.connect(host='127.0.0.1', user='root', passwd='123456', db='caiji')
        # 后面三个依次是数据库连接名、数据库密码、数据库名称

        #self.db = pymysql.connect(self.host, self.user, self.password, self.database, charset='utf8',port=self.port)

        self.cursor = self.db.cursor()


    def open_spider(self, spider):
        pass

    def close_spider(self, spider):
        self.db.ping(reconnect=True)
        self.db.close()

    def process_item(self, item, spider):
        print('+-------------开始写入数据----------------+')
        print('|',item.get('table_name'))
        print('|',item.get('title'))
        print('|', item.get('grab_url'))
        print('+---------------------------------------+')
        table_name = item.get('table_name')
        item.pop('table_name') 
        data = dict(item)
        self._store_dict_to_table(data, table_name)
        return item

    def _store_dict_to_table(self, data: dict, table):
        """
        存入字典数据进入mysql.db中给定table
        :param data: 字典数据
        :param table: 表名称
        """
        keys = ', '.join(data.keys())
        values = ', '.join(['%s'] * len(data))
        sql =f'insert into {table} ({keys}) values ({values})'

        # 防止断开
        self.db.ping(reconnect=True)
        try:
            self.cursor.execute(sql, tuple(data.values()))
        except DataError as e:
            error = str(e)
            print("xxxxx 数据写入失败 xxxxx",error)
        else:
            self.db.commit()
